package com.xmy.cultivate.mapper;

import com.xmy.cultivate.entity.StudentResidueCourse;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xmy.cultivate.entity.TrackDetail;
import com.xmy.cultivate.entity.TrackDetailHistorry;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDate;
import java.util.List;

/**
 * <p>
 * 已分班学生剩余课次记录【缴费率使用】 Mapper 接口
 * </p>
 *
 * @author hpiggy
 * @since 2024-06-19
 */
public interface StudentResidueCourseMapper extends BaseMapper<StudentResidueCourse> {


    @Select({
            "<script>",
            "SELECT",
            "* ",
            "FROM",
            "student_residue_course ",
            "WHERE",
            "year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNum} ",
            "AND subjects_id = ${subjectsId} ",
            "AND student_id = ${studentId} ",
            "AND lesson_type = ${lessonType} ",
            "ORDER BY end_date desc limit 1",
            "</script>"})
    public StudentResidueCourse getOneForDate(@Param("yearPart") Integer yearPart,
                                              @Param("quarterNum") Integer quarterNum,
                                              @Param("subjectsId") Long subjectsId,
                                              @Param("studentId") Long studentId,
                                              @Param("lessonType") Integer lessonType,
                                              @Param("dateNow") LocalDate dateNow);

    /**
     * 获取学生剩余课次
     * @param yearPart
     * @param quarterNum
     * @param lessonType
     * @return
     */
    @Select({
            "<script>",
            "SELECT",
            "t1.*,",
            "SUM( t2.residue_course_count ) AS residue_course_count,",
            "SUM( t2.use_course_count ) AS use_course_count,",
            "SUM( t2.use_price ) AS use_price,",
            "SUM( t2.residue_price ) AS residue_price ",
            "FROM",
            "(",
            "SELECT",
              "t01.school_id,",
              "t01.student_id,",
              "t01.lesson_type,",
              "t01.subjects_id,",
              "t01.year_part,",
              "t01.quarter_num,",
              "t01.reading_status,",
              "t02.id AS grade_id,",
              "t02.teacher_id ",
            "FROM",
              "student_grade AS t01",
              "INNER JOIN grade AS t02 ON t01.grade_id = t02.id ",
            "WHERE",
              "t01.year_part = ${yearPart} ",
              "AND t01.quarter_num = ${quarterNum} ",
              "AND t01.lesson_type = ${lessonType} ",
              "AND t01.is_last = 1 ",
              "AND t01.deleted = 0 ",
              "AND t01.`status` &lt;&gt; 3 ",
              "AND (t01.reading_status IN ( 1, 4, 5, 6 ) OR ( t01.reading_status = 3 AND t01.use_course_count &gt; 0 ))",
            "GROUP BY",
              "t01.student_id,",
              "t01.subjects_id ",
            ") AS t1",
            "INNER JOIN order_detail AS t2 ",
            "WHERE",
            "t1.year_part = t2.year_part ",
            "AND t1.quarter_num = t2.quarter_num ",
            "AND t1.subjects_id = t2.subjects_id ",
            "AND t1.lesson_type = t2.lesson_type ",
            "AND t1.student_id = t2.student_id ",
            "AND t2.`status` &lt;&gt; 2 ",
            "AND t2.deleted = 0 ",
            "GROUP BY",
            "t1.student_id,",
            "t1.subjects_id,",
            "t1.lesson_type",
            "</script>"})
    public List<StudentResidueCourse> getResidueCourse(@Param("yearPart") Integer yearPart,
                                                       @Param("quarterNum") Integer quarterNum,
                                                       @Param("lessonType") Integer lessonType
                                                       );

    @Select({"<script>",
            "SELECT",
                    "* ",
                    "FROM",
                    "((",
                      "SELECT",
                        "t1.*,",
                        "t2.year_part,",
                        "t2.quarter_num ",
                      "FROM",
                        "track_detail AS t1",
                        "INNER JOIN track AS t2 ON t1.track_id = t2.id ",
                      "WHERE",
                        "t1.deleted = 0 ",
                        ") UNION ALL",
                      "(",
                      "SELECT",
                        "t1.*,",
                        "t2.year_part,",
                        "t2.quarter_num ",
                      "FROM",
                        "track_detail AS t1",
                        "INNER JOIN track_market AS t2 ON t1.track_id = t2.id ",
                      "WHERE",
                      "t1.deleted = 0 ",
                    ")) AS t where year_part = ${yearPart} and `quarter_num` = ${quarterNum}",
            "</script>"})
    public List<TrackDetail> getTrackDetailList(@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum);

    @Select({"<script>",
            "SELECT",
                    "* ",
                    "FROM",
                    "((",
                      "SELECT",
                        "t1.*,",
                        "t2.year_part,",
                        "t2.quarter_num ",
                      "FROM",
                        "track_detail_historry AS t1",
                        "INNER JOIN track AS t2 ON t1.track_id = t2.id ",
                      "WHERE",
                        "t1.deleted = 0 ",
                        ") UNION ALL",
                      "(",
                      "SELECT",
                        "t1.*,",
                        "t2.year_part,",
                        "t2.quarter_num ",
                      "FROM",
                        "track_detail_historry AS t1",
                        "INNER JOIN track_market AS t2 ON t1.track_id = t2.id ",
                      "WHERE",
                        "t1.deleted = 0 ",
                      ")) AS t ",
                    "WHERE",
                    "year_part = ${yearPart} ",
                    "AND `quarter_num` = ${quarterNum} ",
                    "AND track_id = ${trackId} ",
                    "AND sourse_id = ${sourseId} ",
                    "ORDER BY",
                    "end_date,id DESC ",
                    "LIMIT 1",
            "</script>"})
    public TrackDetailHistorry getTrackDetailOne(@Param("yearPart") Integer yearPart,
                                                 @Param("quarterNum") Integer quarterNum,
                                                 @Param("trackId") Long trackId,
                                                 @Param("sourseId") Long sourseId);

}
